* Daily disciplinary action


import excel using "$basefiles\10-11 through 14-15 Approved Susps_De-Identified.xlsx", sheet("SY2010-2011") clear firstrow
gen year = 2011

* clean up bad student id
replace studentid = "9049141" if studentid == "452_9049141"
replace studentid = "9026955" if studentid == "452_9026955"
gen nstudentid = real(studentid)
drop studentid
rename nstudentid studentid
tostring gradeleveldesc, gen(sgradeleveldesc)
drop gradeleveldesc
rename sgradeleveldesc gradeleveldesc
replace gradeleveldesc = "" if gradeleveldesc == "."

save "$temp_dir\suspension_2011.dta", replace

import excel using "$basefiles\10-11 through 14-15 Approved Susps_De-Identified.xlsx", sheet("SY2011-2012") clear firstrow
gen year = 2012
save "$temp_dir\suspension_2012.dta", replace

* Combine years
append using "$temp_dir\suspension_2011.dta"

rename gradeleveldesc grade
rename studentid stu_id
rename UniqueUVASchoolCode sch_id
rename actiondesc action
rename infractiondesc infraction
rename infractionleveldesc level
rename startdt start_date
rename enddt end_date

drop Count
duplicates drop stu_id date start_date end_date, force
gsort stu_id date -end_date
duplicates drop stu_id date start_date, force
duplicates drop stu_id date, force

save "$temp_dir\all_suspension_data.dta", replace


*====================
* COMBINE WITH TEACHERS
*====================

** First combine with Roster **
use "$basefiles\\$basefile_studentRoster", clear
keep if year == 2011 | year == 2012
merge m:m stu_id year using "$temp_dir\all_suspension_data.dta", keep(1 3) nogenerate

keep if !mi(date) // now we have teacher X student X date but only for suspensions
*duplicates tag id stu_id subject date, g(dup)
drop if year_dose == 0
*then combine with observation data
merge m:1 id year using "$basefiles\\$basefile_observation", keep(3) nogenerate

*now combine with teacher panel
merge m:1 id year using "$basefiles\\$basefile_teacher", keep(3) nogenerate

save "$temp_dir\suspension_roster.dta", replace





*** Now data for day-by-day analysis:
*====================
* COMBINE WITH TEACHERS
*====================

** First combine with Roster **
use "$basefiles\\$basefile_studentRoster", clear
keep if year == 2011 | year == 2012
merge m:m stu_id year using "$temp_dir\all_suspension_data.dta"

g suspension_record = _merge == 3
drop grade
sort id year
drop if !suspension_record

keep id stu_id subject year year_dose date suspensiondays
drop if year_dose == 0

save "$temp_dir\suspension_roster_daybyday.dta", replace


* create full roster of teachers

use "$basefiles\\$basefile_observation", clear
keep if year == 2011 | year == 2012

merge m:1 id year using "$basefiles\\$basefile_teacher"
rename sch1 sch_id
keep id year obsdate_2p obsdate_2m confdate_2p confdate_2m experience ivaread ivamath gradep sch_id

keep if inlist(gradep, 7, 8, 9, 10, 11)

g grade = ""
replace grade = "4" if gradep == 7
replace grade = "5" if gradep == 8
replace grade = "6" if gradep == 9
replace grade = "7" if gradep == 10
replace grade = "8" if gradep == 11

save "$temp_dir\teacher_roster_for_susp.dta", replace


